package persistence

import (
	"context"

	"Data-API-MCP/backend/internal/domain/entity"
	"Data-API-MCP/backend/internal/domain/repository"
	"database/sql"
	"fmt"
	"time"
)

// MySQLConnectionRepository MySQL连接仓储实现
type MySQLConnectionRepository struct {
	db *sql.DB
}

// NewMySQLConnectionRepository 创建MySQL连接仓储
func NewMySQLConnectionRepository(db *sql.DB) repository.ConnectionRepository {
	return &MySQLConnectionRepository{db: db}
}

// Create 创建连接
func (r *MySQLConnectionRepository) Create(ctx context.Context, connection *entity.Connection) error {
	query := `
		INSERT INTO connections (
			id, name, type, host, port, username, password, database,
			description, max_idle_conns, max_open_conns, conn_max_lifetime,
			status, created_at, updated_at
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	`

	now := time.Now().Unix()
	_, err := r.db.ExecContext(ctx, query,
		connection.ID,
		connection.Name,
		connection.Type,
		connection.Host,
		connection.Port,
		connection.Username,
		connection.Password,
		connection.DatabaseName,
		connection.Description,
		connection.MaxIdleConns,
		connection.MaxOpenConns,
		connection.ConnMaxLifetime,
		connection.Status,
		now,
		now,
	)

	if err != nil {
		return fmt.Errorf("创建连接失败: %v", err)
	}

	connection.CreatedAt = time.Unix(now, 0)
	connection.UpdatedAt = time.Unix(now, 0)
	return nil
}

// Update 更新连接
func (r *MySQLConnectionRepository) Update(ctx context.Context, connection *entity.Connection) error {
	query := `
		UPDATE connections SET
			name = ?, type = ?, host = ?, port = ?, username = ?,
			password = ?, database = ?, description = ?, max_idle_conns = ?,
			max_open_conns = ?, conn_max_lifetime = ?, status = ?, updated_at = ?
		WHERE id = ?
	`

	now := time.Now().Unix()
	result, err := r.db.ExecContext(ctx, query,
		connection.Name,
		connection.Type,
		connection.Host,
		connection.Port,
		connection.Username,
		connection.Password,
		connection.DatabaseName,
		connection.Description,
		connection.MaxIdleConns,
		connection.MaxOpenConns,
		connection.ConnMaxLifetime,
		connection.Status,
		now,
		connection.ID,
	)

	if err != nil {
		return fmt.Errorf("更新连接失败: %v", err)
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("获取影响行数失败: %v", err)
	}

	if rows == 0 {
		return repository.ErrNotFound
	}

	connection.UpdatedAt = time.Unix(now, 0)
	return nil
}

// Delete 删除连接
func (r *MySQLConnectionRepository) Delete(ctx context.Context, id string) error {
	query := `DELETE FROM connections WHERE id = ?`
	result, err := r.db.ExecContext(ctx, query, id)
	if err != nil {
		return fmt.Errorf("删除连接失败: %v", err)
	}

	rows, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("获取影响行数失败: %v", err)
	}

	if rows == 0 {
		return repository.ErrNotFound
	}

	return nil
}

// FindByID 根据ID查找连接
func (r *MySQLConnectionRepository) FindByID(ctx context.Context, id string) (*entity.Connection, error) {
	query := `SELECT * FROM connections WHERE id = ?`
	row := r.db.QueryRowContext(ctx, query, id)

	conn := &entity.Connection{}
	err := row.Scan(
		&conn.ID,
		&conn.Name,
		&conn.Type,
		&conn.Host,
		&conn.Port,
		&conn.Username,
		&conn.Password,
		&conn.DatabaseName,
		&conn.Description,
		&conn.MaxIdleConns,
		&conn.MaxOpenConns,
		&conn.ConnMaxLifetime,
		&conn.Status,
		&conn.CreatedAt,
		&conn.UpdatedAt,
	)

	if err == sql.ErrNoRows {
		return nil, repository.ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("查询连接失败: %v", err)
	}

	return conn, nil
}

// FindByName 根据名称查找连接
func (r *MySQLConnectionRepository) FindByName(ctx context.Context, name string) (*entity.Connection, error) {
	query := `SELECT * FROM connections WHERE name = ?`
	row := r.db.QueryRowContext(ctx, query, name)

	conn := &entity.Connection{}
	err := row.Scan(
		&conn.ID,
		&conn.Name,
		&conn.Type,
		&conn.Host,
		&conn.Port,
		&conn.Username,
		&conn.Password,
		&conn.DatabaseName,
		&conn.Description,
		&conn.MaxIdleConns,
		&conn.MaxOpenConns,
		&conn.ConnMaxLifetime,
		&conn.Status,
		&conn.CreatedAt,
		&conn.UpdatedAt,
	)

	if err == sql.ErrNoRows {
		return nil, repository.ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("查询连接失败: %v", err)
	}

	return conn, nil
}

// List 获取连接列表
func (r *MySQLConnectionRepository) List(ctx context.Context, offset, limit int) ([]*entity.Connection, error) {
	query := `SELECT * FROM connections ORDER BY created_at DESC LIMIT ? OFFSET ?`
	rows, err := r.db.QueryContext(ctx, query, limit, offset)
	if err != nil {
		return nil, fmt.Errorf("查询连接列表失败: %v", err)
	}
	defer rows.Close()

	var connections []*entity.Connection
	for rows.Next() {
		conn := &entity.Connection{}
		err := rows.Scan(
			&conn.ID,
			&conn.Name,
			&conn.Type,
			&conn.Host,
			&conn.Port,
			&conn.Username,
			&conn.Password,
			&conn.DatabaseName,
			&conn.Description,
			&conn.MaxIdleConns,
			&conn.MaxOpenConns,
			&conn.ConnMaxLifetime,
			&conn.Status,
			&conn.CreatedAt,
			&conn.UpdatedAt,
		)
		if err != nil {
			return nil, fmt.Errorf("扫描连接数据失败: %v", err)
		}
		connections = append(connections, conn)
	}

	return connections, nil
}

// Count 获取连接总数
func (r *MySQLConnectionRepository) Count(ctx context.Context) (int64, error) {
	query := `SELECT COUNT(*) FROM connections`
	var count int64
	err := r.db.QueryRowContext(ctx, query).Scan(&count)
	if err != nil {
		return 0, fmt.Errorf("获取连接总数失败: %v", err)
	}
	return count, nil
}

// ListByType 获取指定类型的连接列表
func (r *MySQLConnectionRepository) ListByType(ctx context.Context, dbType string, offset, limit int) ([]*entity.Connection, error) {
	query := `SELECT * FROM connections WHERE type = ? ORDER BY created_at DESC LIMIT ? OFFSET ?`
	rows, err := r.db.QueryContext(ctx, query, dbType, limit, offset)
	if err != nil {
		return nil, fmt.Errorf("查询连接列表失败: %v", err)
	}
	defer rows.Close()

	var connections []*entity.Connection
	for rows.Next() {
		conn := &entity.Connection{}
		err := rows.Scan(
			&conn.ID,
			&conn.Name,
			&conn.Type,
			&conn.Host,
			&conn.Port,
			&conn.Username,
			&conn.Password,
			&conn.DatabaseName,
			&conn.Description,
			&conn.MaxIdleConns,
			&conn.MaxOpenConns,
			&conn.ConnMaxLifetime,
			&conn.Status,
			&conn.CreatedAt,
			&conn.UpdatedAt,
		)
		if err != nil {
			return nil, fmt.Errorf("扫描连接数据失败: %v", err)
		}
		connections = append(connections, conn)
	}

	return connections, nil
}

// CountByType 获取指定类型的连接总数
func (r *MySQLConnectionRepository) CountByType(ctx context.Context, dbType string) (int64, error) {
	query := `SELECT COUNT(*) FROM connections WHERE type = ?`
	var count int64
	err := r.db.QueryRowContext(ctx, query, dbType).Scan(&count)
	if err != nil {
		return 0, fmt.Errorf("获取连接总数失败: %v", err)
	}
	return count, nil
}

// TestConnection 测试连接
func (r *MySQLConnectionRepository) TestConnection(ctx context.Context, connection *entity.Connection) error {
	dbConn, err := NewMySQLConnection(connection)
	if err != nil {
		return err
	}
	defer dbConn.Close()

	return dbConn.Ping(ctx)
}
